# @Time: 2023/1/26 20:12
# @Author: 张江南
# @File: 
# @Project: 代码
import pymysql
import xlwings as xw

con = pymysql.connect(host='localhost', user='root', password='123qweQAZ', database='ry-vue', charset='utf8mb4')
cur = con.cursor()

app = xw.App(visible=False, add_book=False)
app.display_alerts = True  # 显示警报()
app.screen_updating = False  # 屏幕更新(应用中)

# 文件位置：filepath，打开test文档，然后保存，关闭，结束程序
filepath = '../excel/csdn001.xlsx'
wb = app.books.open(filepath)
sh1 = wb.sheets['sheet1']

info = wb.sheets['sheet1'].used_range
total = info.last_cell.row
# total = wb.sheets['sheet1'].use_range.last_cell.row
# total = 200
start = 10000
end = start + 100
num = start
try:
    while num < total:
        s = 'A{}:L{}'.format(start, end)
        data = sh1.range(s).value
        num += len(data)
        start = end + 1
        end = end + 100
        if end > total:
            end = total
        for i, item in enumerate(data):
            flag = True
            for index, tt in enumerate(item):
                if tt is None or tt == "":
                    flag = False
                    break
            if flag:
                item[5] = 1
                item[8] = int(item[8])
                item[9] = int(item[9])
                # data[i] = tuple(item)
                sql1 = 'insert into article_content (content) values (%s)'
                param = (item[11])
                # 执行数据库插入
                cur.execute(sql1, param)
                sql2 = 'insert into article_info (origin,title,pictures,type,author_name,author_id,description,create_date,' \
                       'watch_num,collect_num,remark,content_id) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
                param = (
                    item[0], item[1], item[2], item[3], item[4], item[5], item[6], item[7], item[8], item[9], item[10],
                    cur.lastrowid)
                cur.execute(sql2, param)
                # 提交事务 方法二
                con.commit()

except Exception as e:
    # 如果执行失败要回滚
    con.rollback()
    print("数据库操作异常：\n", e)
finally:
    cur.close()
    # 关闭数据库连接，目的为了释放内存
    con.close()
    wb.close()
    app.quit()
    app.kill()
